package org.lsda.util;

import java.net.URL;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.io.*;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.lsda.job.LsdaAction;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonArray;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

/**
 * @author ChunqiuZeng
 */
public class Util {

	public static Log log = LogFactory.getLog(Util.class);

	/** store the information of db */
	public static Properties dbProp = new Properties();

	/** store the information of broker */
	public static Properties brokerProp = new Properties();

	/** initialize the information when the class is loaded */
	static {
		InputStream dbin = null;
		InputStream brin = null;
		try {
			// FileWriter fstream = new FileWriter("1111111111111.txt");
			//
			// BufferedWriter outx = new BufferedWriter(fstream);
			// outx.write("Hello Java");
			// outx.close();
			//
			// File file = new File("1111111111111.txt");
			// log.info(file.getAbsoluteFile());

			// System.out.println("path:"+Util.class.getClassLoader().getResource("."));

			dbin = new BufferedInputStream(
					new URL("http://localhost/scheduler/conf/db.properties")
							.openStream());
			dbProp.load(dbin);

			log.info(getDBDriver());

			brin = new BufferedInputStream(
					new URL("http://localhost/scheduler/conf/broker.properties")
							.openStream());
			brokerProp.load(brin);

		} catch (IOException e) {
			log.error(e.getMessage());
		} finally {
			try {
				dbin.close();
				brin.close();
			} catch (Throwable ignore) {
			}
		}
	}

	/**
	 * get the url of db
	 * 
	 * @return
	 */
	public static String getDBUrl() {
		return dbProp.getProperty("url");
	}

	/**
	 * get the user of db
	 * 
	 * @return
	 */
	public static String getDBUser() {
		return dbProp.getProperty("user");
	}

	/**
	 * get the password of db
	 * 
	 * @return
	 */
	public static String getDBPasswd() {
		return dbProp.getProperty("password");
	}

	/**
	 * get the driver of db
	 * 
	 * @return
	 */
	public static String getDBDriver() {
		return dbProp.getProperty("driver");
	}

	/**
	 * get the queue name
	 * 
	 * @return
	 */
	public static String getBRQueName() {
		return brokerProp.getProperty("queueName");
	}

	/**
	 * get the url of broker
	 * 
	 * @return
	 */
	public static String getBRUrl() {
		return brokerProp.getProperty("url");
	}

	/**
	 * get the user of broker
	 * 
	 * @return
	 */
	public static String getBRUser() {
		return brokerProp.getProperty("userName");
	}

	/**
	 * get the password of broker
	 * 
	 * @return
	 */
	public static String getBRPasswd() {
		return brokerProp.getProperty("password");
	}

	/**
	 * get a db connection
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Connection getConnection() throws ClassNotFoundException,
			SQLException {
		Connection conn = null;
		String url = getDBUrl();
		String driver = getDBDriver();
		String user = getDBUser();
		String pwd = getDBPasswd();

		Class.forName(driver);
		conn = DriverManager.getConnection(url, user, pwd);

		return conn;
	}

	/**
	 * get an auto-incremental id
	 * 
	 * @param IDName
	 * @return
	 */
	public static int autoIncrementID(String IDName) {
		String sqlInsert = "insert into " + IDName + " (id) values(null)";
		String sqlDelete = "delete from " + IDName + " where id<";
		int id = -1;
		Connection conn = null;
		try {
			conn = getConnection();
			// conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(sqlInsert, Statement.RETURN_GENERATED_KEYS);
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			rs.close();
			stmt.executeUpdate(sqlDelete + id);
			// conn.commit();
			stmt.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (Throwable igrone) {
			}
		}
		return id;
	}

	/**
	 * insert a job
	 * 
	 * @param conn
	 * @param job_Group
	 * @param job_name
	 * @param schedSeq
	 * @return
	 * @throws SQLException
	 */
	public static boolean insertJob(Connection conn, String job_Group,
			String job_name, int schedSeq, String trigger_Group,
			String trigger_Name) throws SQLException {
		boolean ret = false;
		String sql = "insert into LSDA_JOB(SCHED_SEQ,JOB_GROUP,JOB_NAME,TRIGGER_GROUP,TRIGGER_NAME) values(?,?,?,?,?)";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, schedSeq);
		pstmt.setString(2, job_Group);
		pstmt.setString(3, job_name);
		pstmt.setString(4, trigger_Group);
		pstmt.setString(5, trigger_Name);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * insert an action
	 * 
	 * @param conn
	 * @param sched_seq
	 * @param action_seq
	 * @param prg_name
	 * @param prg_param
	 * @return
	 * @throws SQLException
	 */
	public static boolean insertAction(Connection conn, int sched_seq,
			int action_seq, String prg_name, String prg_param)
			throws SQLException {
		boolean ret = false;
		String sql = "insert into LSDA_ACTION(SCHED_SEQ,ACTION_SEQ,PRG_NAME,PRG_PARAM,STATUS) values(?,?,?,?,-1)";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, sched_seq);
		pstmt.setInt(2, action_seq);
		pstmt.setString(3, prg_name);
		pstmt.setString(4, prg_param);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * mark the status of action to be started
	 * 
	 * @param conn
	 * @param sched_seq
	 * @param action_seq
	 * @return
	 * @throws SQLException
	 */
	public static boolean markActionStart(Connection conn, int sched_seq,
			int action_seq) throws SQLException {
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=1,BEG_TIME=now(),INFO=\"sending\" where SCHED_SEQ=? and ACTION_SEQ=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, sched_seq);
		pstmt.setInt(2, action_seq);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * mark the action to be running
	 * 
	 * @param conn
	 * @param sched_seq
	 * @param action_seq
	 * @param host
	 * @param pid
	 * @return
	 * @throws SQLException
	 */
	public static boolean markActionRunning(Connection conn, int sched_seq,
			int action_seq, String host, int pid) throws SQLException {
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=1, HOST=?,PID=?,INFO=\"running\",BEG_TIME=now() where SCHED_SEQ=? and ACTION_SEQ=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, host);
		pstmt.setInt(2, pid);
		pstmt.setInt(3, sched_seq);
		pstmt.setInt(4, action_seq);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	public static boolean markActionRunning(int sched_seq, int action_seq,
			String host, int pid) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=1, HOST=?,PID=?,INFO=\"running\",BEG_TIME=now() where SCHED_SEQ=? and ACTION_SEQ=?";
		try {
			conn = Util.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, host);
			pstmt.setInt(2, pid);
			pstmt.setInt(3, sched_seq);
			pstmt.setInt(4, action_seq);
			ret = pstmt.execute();
			// conn.commit();
			pstmt.close();
		} finally {
			try {
				conn.close();
			} catch (Exception ignore) {
			}
		}

		return ret;
	}

	/**
	 * mark the end of an action
	 * 
	 * @param conn
	 * @param sched_seq
	 * @param action_seq
	 * @param status
	 * @param info
	 * @return
	 * @throws SQLException
	 */
	public static boolean markActionEnd(Connection conn, int sched_seq,
			int action_seq, int status, String info) throws SQLException {
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=?,END_TIME=now(),INFO=substr(?,1,500) where SCHED_SEQ=? and ACTION_SEQ=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, status);
		pstmt.setString(2, info);
		pstmt.setInt(3, sched_seq);
		pstmt.setInt(4, action_seq);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * pause a job
	 * 
	 * @param conn
	 * @param sched_seq
	 * @return
	 * @throws SQLException
	 */
	public static boolean pauseJob(Connection conn, int sched_seq)
			throws SQLException {
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=-2 where SCHED_SEQ=? and ACTION_SEQ in "
				+ "(select ACTION_SEQ from LSDA_ACTION where SCHED_SEQ=? and STATUS = -1 order by ACTION_SEQ limit 1)";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, sched_seq);
		pstmt.setInt(2, sched_seq);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;

	}

	/**
	 * continue to do job
	 * 
	 * @param conn
	 * @param sched_seq
	 * @return
	 * @throws SQLException
	 */
	public static boolean resumeJob(Connection conn, int sched_seq)
			throws SQLException {
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=-1 where SCHED_SEQ=? and STATUS=-2";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, sched_seq);
		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * get the status of job
	 * 
	 * @param conn
	 * @param sched_seq
	 * @return
	 * @throws SQLException
	 */
	public static int getJobStatus(Connection conn, int sched_seq)
			throws SQLException {
		int status = 0;
		String sql = "select STATUS from LSDA_ACTION where SCHED_SEQ=? and STATUS <>0 order by ACTION_SEQ ASC limit 1";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, sched_seq);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next())
			status = rs.getInt("STATUS");
		rs.close();
		pstmt.close();
		return status;
	}

	public static int countJobs(Connection conn) throws SQLException {
		String sql = "select count(*) from (select j.SCHED_SEQ AS SCHED_SEQ, j.JOB_NAME AS JOB_NAME, "
				+ "j.JOB_GROUP AS JOB_GROUP,j.TRIGGER_NAME AS TRIGGER_NAME,j.TRIGGER_GROUP AS TRIGGER_GROUP, t.STATUS AS STATUS "
				+ "from    LSDA_JOB j, (select  SCHED_SEQ, STATUS   from  LSDA_ACTION where BEG_TIME is not null  order by ACTION_SEQ desc) t "
				+ "where     j.SCHED_SEQ = t.SCHED_SEQ group by j.SCHED_SEQ) B";
		// System.out.println(sql);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		int count = 0;
		if (rs.next())
			count = rs.getInt(1);

		rs.close();
		stmt.close();
		return count;
	}

	public static JsonArray listJobs(Connection conn, int jtPageSize,
			int jtStartIndex, String jtSorting) throws SQLException {
		String sql = "select j.SCHED_SEQ AS SCHED_SEQ, j.JOB_NAME AS JOB_NAME, "
				+ "j.JOB_GROUP AS JOB_GROUP,j.TRIGGER_NAME AS TRIGGER_NAME,j.TRIGGER_GROUP AS TRIGGER_GROUP, t.STATUS AS STATUS "
				+ "from    LSDA_JOB j, (select  SCHED_SEQ, STATUS   from  LSDA_ACTION where BEG_TIME is not null  order by ACTION_SEQ desc) t "
				+ "where     j.SCHED_SEQ = t.SCHED_SEQ group by j.SCHED_SEQ order by "
				+ jtSorting + " limit " + jtStartIndex + "," + jtPageSize;
		// System.out.println(sql);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		JsonArray retArray = new JsonArray();
		while (rs.next()) {
			JsonObject record = new JsonObject();
			record.addProperty("SchedSeq", rs.getInt("SCHED_SEQ"));
			record.addProperty("JobName", rs.getString("JOB_NAME"));
			record.addProperty("JobGroup", rs.getString("JOB_GROUP"));
			record.addProperty("TriggerName", rs.getString("TRIGGER_NAME"));
			record.addProperty("TriggerGroup", rs.getString("TRIGGER_GROUP"));
			record.addProperty("JobStatus", rs.getInt("STATUS"));
			retArray.add(record);
		}

		rs.close();
		stmt.close();

		return retArray;
	}

	/**
	 * mark a job to be ready status
	 * 
	 * @param conn
	 * @param sched_seq
	 * @return
	 * @throws SQLException
	 */
	public static boolean markJobReady(Connection conn, int sched_seq)
			throws SQLException {
		boolean ret = false;
		String sql = "update LSDA_ACTION set STATUS=-1 where SCHED_SEQ=? and STATUS<>0 and ACTION_SEQ>0";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, sched_seq);

		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * clear all the actions of a job
	 * 
	 * @param conn
	 * @param sched_seq
	 * @return
	 * @throws SQLException
	 */
	public static boolean clearAction(Connection conn, int sched_seq)
			throws SQLException {
		boolean ret = false;
		String sql = "delete from LSDA_ACTION where SCHED_SEQ=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, sched_seq);

		ret = pstmt.execute();
		// conn.commit();
		pstmt.close();
		return ret;
	}

	/**
	 * get the status of an action
	 * 
	 * @param conn
	 * @param sched_seq
	 * @param action_seq
	 * @return
	 * @throws SQLException
	 */
	public static int getActionStatus(Connection conn, int sched_seq,
			int action_seq) throws SQLException {
		int ret = Integer.MIN_VALUE;
		String sql = "select STATUS from LSDA_ACTION where SCHED_SEQ=? and ACTION_SEQ=?";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setInt(1, sched_seq);
		pstmt.setInt(2, action_seq);

		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			ret = rs.getInt(1);
		}

		rs.close();
		pstmt.close();

		return ret;
	}

	/**
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static ArrayList<String> listProgram(Connection conn)
			throws SQLException {
		String sql = "SELECT PRG_NAME FROM LSDA_PROG order by 1 ";
		// System.out.println(sql);
		ArrayList<String> ret = new ArrayList<String>();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			ret.add(rs.getString(1));
		}
		return ret;
	}

	/**
	 * @param conn
	 * @param jtPageSize
	 * @param jtStartIndex
	 * @param jtSorting
	 * @return
	 * @throws SQLException
	 */
	public static JsonArray listPrograms(Connection conn, int jtPageSize,
			int jtStartIndex, String jtSorting) throws SQLException {
		String sql = "SELECT CATEGORY,PRG_NAME,PRG_AUTHOR,PRG_DESC FROM LSDA_PROG ORDER BY "
				+ jtSorting + " limit " + jtStartIndex + "," + jtPageSize;

		JsonArray retArray = new JsonArray();

		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		while (rs.next()) {
			JsonObject record = new JsonObject();
			record.addProperty("Category", rs.getString("CATEGORY"));
			record.addProperty("PrgName", rs.getString("PRG_NAME"));
			record.addProperty("Author", rs.getString("PRG_AUTHOR"));
			record.addProperty("PrgDesc", rs.getString("PRG_DESC"));
			retArray.add(record);
		}
		rs.close();
		stmt.close();
		return retArray;
	}

	/**
	 * @param conn
	 * @param prgName
	 * @return
	 * @throws SQLException
	 */
	public static boolean deleteProgram(Connection conn, String prgName)
			throws SQLException {
		String sql = "DELETE FROM LSDA_PROG where PRG_NAME=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, prgName);
		boolean ret = pstmt.execute();
		pstmt.close();
		return ret;

	}

	/**
	 * @param conn
	 * @param prgName
	 * @param paramName
	 * @return
	 * @throws SQLException
	 */
	public static boolean deleteParam(Connection conn, String prgName,
			String paramName) throws SQLException {
		String sql = "DELETE FROM LSDA_PARAM where PRG_NAME=? and NAME=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, prgName);
		pstmt.setString(2, paramName);
		boolean ret = pstmt.execute();
		pstmt.close();
		return ret;

	}

	/**
	 * @param conn
	 * @param category
	 * @param prgName
	 * @param author
	 * @param desc
	 * @return
	 * @throws SQLException
	 */
	public static JsonObject createProgram(Connection conn, String category,
			String prgName, String author, String desc) throws SQLException {
		String sql = "insert into LSDA_PROG(CATEGORY,PRG_NAME,PRG_AUTHOR,PRG_DESC)values(?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, category);
		pstmt.setString(2, prgName);
		pstmt.setString(3, author);
		pstmt.setString(4, desc);
		boolean ret = pstmt.execute();
		pstmt.close();
		JsonObject json = new JsonObject();
		json.addProperty("Category", category);
		json.addProperty("PrgName", prgName);
		json.addProperty("Author", author);
		json.addProperty("PrgDesc", desc);
		return json;

	}

	public static JsonObject createProgram(Connection conn, String category,
			String prgName, String name, int type, String desc)
			throws SQLException {
		String sql = "insert into LSDA_PARAM(PRG_NAME,NAME,TYPE,DESCRIPTION)values(?,?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, prgName);
		pstmt.setString(2, name);
		pstmt.setInt(3, type);
		pstmt.setString(4, desc);
		boolean ret = pstmt.execute();
		pstmt.close();
		JsonObject json = new JsonObject();
		json.addProperty("Category", category);
		json.addProperty("PrgName", prgName);
		json.addProperty("ParamName", name);
		json.addProperty("ParamType", type);
		json.addProperty("ParamDesc", desc);
		return json;

	}

	/**
	 * @param conn
	 * @param prgName
	 * @param category
	 * @param author
	 * @param desc
	 * @throws SQLException
	 */
	public static void updateProg(Connection conn, String prgName,
			String category, String author, String desc) throws SQLException {
		String sql = "update LSDA_PROG set CATEGORY=?,PRG_AUTHOR=?,PRG_DESC=? where PRG_NAME=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, category);
		pstmt.setString(2, author);
		pstmt.setString(3, desc);
		pstmt.setString(4, prgName);
		pstmt.execute();

		pstmt.close();
	}

	/**
	 * @param conn
	 * @param prgName
	 * @param name
	 * @param type
	 * @param desc
	 * @throws SQLException
	 */
	public static void updateParam(Connection conn, String prgName,
			String name, int type, String desc) throws SQLException {
		String sql = "update LSDA_PARAM set TYPE=?,DESCRIPTION=? where PRG_NAME=? and NAME=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, type);
		pstmt.setString(2, desc);
		pstmt.setString(3, prgName);
		pstmt.setString(4, name);
		pstmt.execute();

		pstmt.close();
	}

	/**
	 * @param conn
	 * @param prgName
	 * @return
	 * @throws SQLException
	 */
	public static JsonArray listParams(Connection conn, String prgName)
			throws SQLException {
		String sql = "select NAME,TYPE,DESCRIPTION from LSDA_PARAM where PRG_NAME=? ORDER BY NAME";
		JsonArray retArray = new JsonArray();
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, prgName);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			JsonObject record = new JsonObject();
			record.addProperty("ParamName", rs.getString("NAME"));
			record.addProperty("ParamType", rs.getInt("TYPE"));
			record.addProperty("ParamDesc", rs.getString("DESCRIPTION"));
			retArray.add(record);
		}
		System.out.println(retArray);
		return retArray;
	}

	public static int countPrograms(Connection conn) throws SQLException {
		String sql = "select count(*) from LSDA_PROG";
		// System.out.println(sql);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);

		int count = 0;
		if (rs.next())
			count = rs.getInt(1);

		rs.close();
		stmt.close();
		return count;
	}

	/**
	 * get the first action of a job
	 * 
	 * @param conn
	 * @param sched_seq
	 * @param status
	 * @return
	 * @throws SQLException
	 */
	public static LsdaAction getFirstAction(Connection conn, int sched_seq,
			int status) throws SQLException {
		LsdaAction retAction = null;
		String sql = "select ACTION_SEQ,PRG_NAME,PRG_PARAM,HOST,PID,INFO,BEG_TIME,END_TIME "
				+ "from LSDA_ACTION where SCHED_SEQ=? and STATUS=? order by ACTION_SEQ limit 1";

		PreparedStatement apstmt = conn.prepareStatement(sql);
		apstmt.setInt(1, sched_seq);
		apstmt.setInt(2, status);

		ResultSet rs = apstmt.executeQuery();
		if (rs.next()) {
			retAction = new LsdaAction();
			retAction.setSchedSeq(sched_seq);
			retAction.setStatus(status);
			retAction.setActionSeq(rs.getInt("ACTION_SEQ"));
			retAction.setPrgName(rs.getString("PRG_NAME"));
			retAction.setPrgParam(rs.getString("PRG_PARAM"));
			retAction.setHost(rs.getString("HOST"));
			retAction.setPid(rs.getInt("PID"));
			retAction.setInfo(rs.getString("INFO"));
			retAction.setBegTime(rs.getDate("BEG_TIME"));
			retAction.setEndTime(rs.getDate("END_TIME"));
		}

		rs.close();
		apstmt.close();
		return retAction;
	}

	/**
	 * get the next action to run
	 * 
	 * @param conn
	 * @param sched_seq
	 * @return
	 * @throws SQLException
	 */
	public static LsdaAction getNextActionToRun(Connection conn, int sched_seq,
			int act_seq) throws SQLException {
		LsdaAction retAction = null;
		String sql = "select STATUS, ACTION_SEQ,PRG_NAME,PRG_PARAM,HOST,PID,INFO,BEG_TIME,END_TIME "
				+ "from LSDA_ACTION where SCHED_SEQ=? and STATUS<>0  and ACTION_SEQ>? order by ACTION_SEQ limit 1";

		PreparedStatement apstmt = conn.prepareStatement(sql);
		apstmt.setInt(1, sched_seq);
		apstmt.setInt(2, act_seq);

		ResultSet rs = apstmt.executeQuery();
		if (rs.next()) {
			retAction = new LsdaAction();
			retAction.setSchedSeq(sched_seq);
			retAction.setStatus(rs.getInt("STATUS"));
			retAction.setActionSeq(rs.getInt("ACTION_SEQ"));
			retAction.setPrgName(rs.getString("PRG_NAME"));
			retAction.setPrgParam(rs.getString("PRG_PARAM"));
			retAction.setHost(rs.getString("HOST"));
			retAction.setPid(rs.getInt("PID"));
			retAction.setInfo(rs.getString("INFO"));
			retAction.setBegTime(rs.getDate("BEG_TIME"));
			retAction.setEndTime(rs.getDate("END_TIME"));
		}

		rs.close();
		apstmt.close();
		return retAction;
	}

	/**
	 * @param date
	 * @return
	 */
	public static String dateString(Date date) {
		if (date == null)
			return "";
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		return dateFormat.format(date);
	}

	/**
	 * @param conn
	 * @param SchedSeq
	 * @return
	 * @throws SQLException
	 */
	public static JsonArray listAction(Connection conn, int SchedSeq)
			throws SQLException {

		String sql = "SELECT ACTION_SEQ,PRG_NAME,PRG_PARAM,BEG_TIME,END_TIME,STATUS,INFO"
				+ " FROM LSDA_ACTION WHERE SCHED_SEQ=? ORDER BY ACTION_SEQ ASC";

		PreparedStatement apstmt = conn.prepareStatement(sql);
		apstmt.setInt(1, SchedSeq);

		ResultSet rs = apstmt.executeQuery();
		JsonArray retArray = new JsonArray();
		while (rs.next()) {
			JsonObject record = new JsonObject();
			record.addProperty("ActionSeq", rs.getInt("ACTION_SEQ"));
			record.addProperty("PrgName", rs.getString("PRG_NAME"));
			record.addProperty("PrgParam", rs.getString("PRG_PARAM"));
			record.addProperty("Start", rs.getString("BEG_TIME"));
			record.addProperty("End", rs.getString("END_TIME"));
			record.addProperty("ActStatus", rs.getInt("STATUS"));
			record.addProperty("Info", "<p>" + rs.getString("INFO") + "</p>");
			retArray.add(record);
		}

		rs.close();
		apstmt.close();

		return retArray;

	}

	/**
	 * @param args
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static void main(String[] args) {
		System.out.println(Util.autoIncrementID("jobid"));
		System.out.println(Util.getBRUrl());
		System.out.println(Util.getDBDriver());
	}

}
